﻿<cfsilent>
	<cfscript>

		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		sql = "SELECT 
					a.stuchg_id, d.sct_name, 
					c.academic_year, c.academic_term,
					a.stu_source, a.stu_newlocation, 
					b.stu_id, b.stu_name, 
					case when a.institute_name is null then '-' else a.institute_name end institute_name, 
					case when a.subject_name is null then '-' else a.subject_name end subject_name, 
					case when a.grade is null then '-' else a.grade end grade, 
					case when a.class_name is null then '-' else a.class_name end class_name, 
					a.op_description, a.change_date, 
					case when a.approve_docid is null then '-' else a.approve_docid end approve_docid, 
					a.approve_date
					FROM t_stuchange a 
					   INNER JOIN t_student b ON b.stu_id = a.stu_id  
					   INNER JOIN t_term c ON c.term_id = a.term_id 
					   INNER JOIN t_stuchange_type d ON d.sct_code = a.sct_code 
					ORDER BY 
						a.change_date DESC, a.stu_id";
						
		queryObj = new Query( datasource=application.dnsSlave );

		rs_history = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( "学籍异动历史记录", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( "学籍异动历史记录", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "流水号,异动类别,学年,学期,异动来源,异动去向,学号,姓名,原学院,原专业,原年级,原班级,异动说明,变更日期,审批文号,审批日期");
		
		row = 1;
		
		for (a = 1; a lte rs_history.recordCount; a++) {
			
			row++;
			
			/* 构建学生信息 */
			
			/* 构建考生数据 */
			rowData = rs_history["stuchg_id"][a]
						& "," & rs_history["sct_name"][a]
						& "," & rs_history["academic_year"][a]
						& "," & rs_history["academic_term"][a]
						& "," & rs_history["stu_source"][a]
						& "," & rs_history["stu_newlocation"][a]
						& "," & rs_history["stu_id"][a]
						& "," & rs_history["stu_name"][a]
						& "," & rs_history["institute_name"][a]
						& "," & rs_history["subject_name"][a]
						& "," & rs_history["grade"][a]
						& "," & rs_history["class_name"][a]
						& "," & rs_history["op_description"][a]
						& "," & datetimeAdvice.formatDatetimeString(rs_history["change_date"][a], 'YYYY/MM/DD')
						& "," & rs_history["approve_docid"][a]
						& "," & datetimeAdvice.formatDateString(rs_history["approve_date"][a], 'YYYY/MM/DD');
						
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);

		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>